﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;
using System.Collections;

namespace DAL
{
    public class ClassesDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoClasses model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Classes]");
            sql.Append("([CLA_ID],[CLA_CODE],[CLA_NAME],[CLA_ACADEMY],[CLA_SHORT],[CLA_ENNAME],[CLA_STUNUM],[CLA_INDATE],[CLA_MAJOR],[CLA_SYSTYPE],[CLA_LOCAL],[CLA_REMARK],[CLA_GRADE],[CLA_IsShow])");
            sql.Append("VALUES");
            sql.Append("(@CLA_ID,@CLA_CODE,@CLA_NAME,@CLA_ACADEMY,@CLA_SHORT,@CLA_ENNAME,@CLA_STUNUM,@CLA_INDATE,@CLA_MAJOR,@CLA_SYSTYPE,@CLA_LOCAL,@CLA_REMARK,@CLA_GRADE,@CLA_IsShow)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoClasses model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Classes] SET ");
            sql.Append("CLA_CODE=@CLA_CODE,CLA_SHORT=@CLA_SHORT,CLA_ENNAME=@CLA_ENNAME,CLA_STUNUM=@CLA_STUNUM,CLA_INDATE=@CLA_INDATE,CLA_MAJOR=@CLA_MAJOR,CLA_SYSTYPE=@CLA_SYSTYPE,CLA_LOCAL=@CLA_LOCAL,CLA_REMARK=@CLA_REMARK,CLA_GRADE=@CLA_GRADE,CLA_IsShow=@CLA_IsShow");
            sql.Append(",CLA_NAME=@CLA_NAME");
            sql.Append(",CLA_ACADEMY=@CLA_ACADEMY");
            sql.Append(" WHERE ");
            sql.Append("CLA_ID=@CLA_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string cLA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Classes] SET ");
            sql.Append(" CLA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("CLA_ID=@CLA_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = cLA_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

         /// <summary>
        /// 通过学院的id来删除班级
        /// </summary>
        /// <param name="academyId">学院id</param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteClassesByAcademyId(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Classes] SET ");
            sql.Append(" CLA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("CLA_ACADEMY=@CLA_ACADEMY");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        /// <summary>
        /// 通过专业id来删除班级
        /// </summary>
        /// <param name="majorId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteClassesByMajorId(string majorId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Classes] SET ");
            sql.Append(" CLA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("CLA_MAJOR=@CLA_MAJOR");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_MAJOR", SqlDbType.NVarChar, 50);
            parameter[0].Value = majorId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        ///// <summary>
        ///// 批量删除班级
        ///// </summary>
        ///// <param name="classesId">班级id数组</param>
        ///// <returns></returns>
        //public int DleteMoreClasses(ArrayList classesArray)
        //{
        //    if (classesArray.Count > 0)
        //    {
        //        StringBuilder classesid = new StringBuilder();
        //        for(int i=0; i<classesArray.Count-1;i++)
        //        {
        //            classesid.Append("'");
        //            classesid.Append(classesArray[i]);
        //            classesid.Append("',");
        //        }
        //        classesid.Append("'");
        //        classesid.Append(classesArray[classesArray.Count-1]);
        //        classesid.Append("'");
        //        StringBuilder sql = new StringBuilder();
        //        sql.Append("DELETE FROM Classes WHERE CLA_ID in (" + classesid + ")");
        //        return dbhelper.ExcuteNoQuery(sql.ToString());
        //    }
        //    else
        //        return 0;
        //}

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoClasses GetModel(string cLA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CLA_ID,CLA_CODE,CLA_NAME,CLA_ACADEMY,CLA_SHORT,CLA_ENNAME,CLA_STUNUM,CLA_INDATE,CLA_MAJOR,CLA_SYSTYPE,CLA_LOCAL,CLA_REMARK,CLA_GRADE,CLA_IsShow,LOC_NAME,ACA_NAME,MAJ_NAME ");
            sql.Append("FROM Classes AS C LEFT JOIN Academy AS A ON C.CLA_ACADEMY=A.ACA_ID LEFT JOIN Major AS M ON C.CLA_MAJOR=M.MAJ_ID LEFT JOIN Local AS L ON C.CLA_LOCAL=L.LOC_ID");
            sql.Append(" WHERE ");
            sql.Append("CLA_ID=@CLA_ID AND CLA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = cLA_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoClasses> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoClasses> GetClassesByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CLA_ID,CLA_CODE,CLA_NAME,CLA_ACADEMY,CLA_SHORT,CLA_ENNAME,CLA_STUNUM,CLA_INDATE,CLA_MAJOR,CLA_SYSTYPE,CLA_LOCAL,CLA_REMARK,CLA_GRADE,CLA_IsShow,LOC_NAME,ACA_NAME,MAJ_NAME ");
            sql.Append(" FROM Classes AS C LEFT JOIN Academy AS A ON C.CLA_ACADEMY=A.ACA_ID LEFT JOIN Major AS M ON C.CLA_MAJOR=M.MAJ_ID LEFT JOIN Local AS L ON C.CLA_LOCAL=L.LOC_ID");
            sql.Append(" WHERE ");
            sql.Append("CLA_ACADEMY=@CLA_ACADEMY AND  CLA_IsShow='true' ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoClasses> list = GetList(table);
            return list;
        }
        public IList<BoClasses> GetClassesByMajor(string majorId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CLA_ID,CLA_CODE,CLA_NAME,CLA_ACADEMY,CLA_SHORT,CLA_ENNAME,CLA_STUNUM,CLA_INDATE,CLA_MAJOR,CLA_SYSTYPE,CLA_LOCAL,CLA_REMARK,CLA_GRADE,CLA_IsShow,LOC_NAME,ACA_NAME,MAJ_NAME ");
            sql.Append(" FROM Classes AS C LEFT JOIN Academy AS A ON C.CLA_ACADEMY=A.ACA_ID LEFT JOIN Major AS M ON C.CLA_MAJOR=M.MAJ_ID LEFT JOIN Local AS L ON C.CLA_LOCAL=L.LOC_ID");
            sql.Append(" WHERE ");
            sql.Append("CLA_MAJOR=@CLA_MAJOR AND  CLA_IsShow='true' ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_MAJOR", SqlDbType.NVarChar, 50);
            parameter[0].Value = majorId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoClasses> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 根据条件获取相应的班级数据
        /// </summary>
        /// <param name="sqlcon">sql条件</param>
        /// <returns></returns>
        public List<BoClasses> GetAllClasses(object sqlcon)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT CLA_ID,CLA_CODE,CLA_NAME,CLA_ACADEMY,CLA_SHORT,CLA_ENNAME,CLA_STUNUM,CLA_INDATE,CLA_MAJOR,CLA_SYSTYPE,CLA_LOCAL,CLA_REMARK,CLA_GRADE,CLA_IsShow,LOC_NAME,ACA_NAME,MAJ_NAME ");
            sql.Append(" FROM Classes AS C LEFT JOIN Academy AS A ON C.CLA_ACADEMY=A.ACA_ID LEFT JOIN Major AS M ON C.CLA_MAJOR=M.MAJ_ID LEFT JOIN Local AS L ON C.CLA_LOCAL=L.LOC_ID");
            sql.Append(" WHERE ");
            sql.Append(" CLA_IsShow='true'"); 
            if (sqlcon != null && !string.IsNullOrEmpty(sqlcon.ToString()))
            {
                sql.Append("and  CLA_IsShow='true'");
                sql.Append(sqlcon);
            }
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoClasses> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

          /// <summary>
        /// 得到分页的数据列表
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public IList<BoClasses> getPageCompetitions(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT CLA_ID,CLA_CODE,CLA_NAME,CLA_ACADEMY,CLA_SHORT,CLA_ENNAME,CLA_STUNUM,CLA_INDATE,CLA_MAJOR,CLA_SYSTYPE,CLA_LOCAL,CLA_REMARK,CLA_GRADE,CLA_IsShow,LOC_NAME,ACA_NAME,MAJ_NAME ");
            sql.Append(" FROM Classes AS C LEFT JOIN Academy AS A ON C.CLA_ACADEMY=A.ACA_ID LEFT JOIN Major AS M ON C.CLA_MAJOR=M.MAJ_ID LEFT JOIN Local AS L ON C.CLA_LOCAL=L.LOC_ID");
            sql.Append(" WHERE ");
            sql.Append(" CLA_IsShow='true'"); 
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append(" and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            IList<BoClasses> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }
        private List<BoClasses> GetList(DataTable table)
        {
            List<BoClasses> list = new List<BoClasses>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoClasses model = new BoClasses();
                    model.CLA_ID = (string)table.Rows[i]["CLA_ID"];
                    model.CLA_CODE = table.Rows[i]["CLA_CODE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_CODE"];
                    model.CLA_NAME = table.Rows[i]["CLA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_NAME"];
                    model.CLA_ACADEMY = table.Rows[i]["CLA_ACADEMY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_ACADEMY"];
                    model.CLA_SHORT = table.Rows[i]["CLA_SHORT"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_SHORT"];
                    model.CLA_ENNAME = table.Rows[i]["CLA_ENNAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_ENNAME"];
                    model.CLA_STUNUM = table.Rows[i]["CLA_STUNUM"] == System.DBNull.Value ? 0 : (int)table.Rows[i]["CLA_STUNUM"];
                    model.CLA_INDATE = table.Rows[i]["CLA_INDATE"] == System.DBNull.Value ? DateTime.Today : (DateTime)table.Rows[i]["CLA_INDATE"];
                    model.CLA_MAJOR = table.Rows[i]["CLA_MAJOR"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_MAJOR"];
                    model.CLA_SYSTYPE = table.Rows[i]["CLA_SYSTYPE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_SYSTYPE"];
                    model.CLA_LOCAL = table.Rows[i]["CLA_LOCAL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_LOCAL"];
                    model.CLA_REMARK = table.Rows[i]["CLA_REMARK"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_REMARK"];
                    model.CLA_GRADE = table.Rows[i]["CLA_GRADE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CLA_GRADE"];
                    model.CLA_IsShow = table.Rows[i]["CLA_IsShow"] == System.DBNull.Value ? true  : (bool)table.Rows[i]["CLA_IsShow"];
                    model.ACA_NAME = table.Rows[i]["ACA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["ACA_NAME"];
                    model.LOC_NAME = table.Rows[i]["LOC_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["LOC_NAME"];
                    model.MAJ_NAME = table.Rows[i]["MAJ_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MAJ_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary> 
        /// 通过班级编号来获取班级id
        /// </summary>
        /// <param name="ClassCode"></param>
        /// <returns></returns>
        public string GetIdByName(string ClassCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT CLA_ID FROM Classes");
            sql.Append(" WHERE ");
            sql.Append("CLA_CODE=@ClassCode AND CLA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ClassCode", SqlDbType.NVarChar, 50);
            parameter[0].Value = ClassCode;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            if (table.Rows.Count > 0)
                return table.Rows[0][0].ToString();
            else
                return null;
        }

        /// <summary>
        /// 通过班级编号以及专业编号来获取相应的对象
        /// </summary>
        /// <param name="ClassCode">班级编号</param>
        /// <param name="MajorCode">专业编号</param>
        /// <returns>返回获得的对象</returns>
        public BoClasses GetClassByCodeAndMajorCodeAndAcademyName(string ClassCode, string MajorCode, string AcademyName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT CLA_ID,CLA_CODE,CLA_NAME,CLA_ACADEMY,CLA_SHORT,CLA_ENNAME,CLA_STUNUM,CLA_INDATE,CLA_MAJOR,CLA_SYSTYPE,CLA_LOCAL,CLA_REMARK,CLA_GRADE,CLA_IsShow,LOC_NAME,ACA_NAME,MAJ_NAME ");
            sql.Append(" FROM Classes AS C LEFT JOIN Academy AS A ON C.CLA_ACADEMY=A.ACA_ID LEFT JOIN Major AS M ON C.CLA_MAJOR=M.MAJ_ID LEFT JOIN Local AS L ON C.CLA_LOCAL=L.LOC_ID");
            sql.Append(" WHERE ");
            sql.Append(" CLA_IsShow='true' and ");
            sql.Append("CLA_CODE=@CLA_CODE and MAJ_CODE=@MAJ_CODE and ACA_NAME=@ACA_NAME ");
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@CLA_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_NAME",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = ClassCode;
            parameter[1].Value = MajorCode;
            parameter[2].Value = AcademyName;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(),parameter);
            IList<BoClasses> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
            
        }
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoClasses model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@CLA_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_ACADEMY",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_SHORT",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_ENNAME",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_STUNUM",SqlDbType.Int ,50),
                new SqlParameter("@CLA_INDATE",SqlDbType.DateTime,50),
                new SqlParameter("@CLA_MAJOR",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_SYSTYPE",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_LOCAL",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_REMARK",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_GRADE",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_IsShow",SqlDbType.Bit,2),
                new SqlParameter("@ACA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@LOC_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@MAJ_NAME",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.CLA_ID;
            parameters[1].Value = model.CLA_CODE;
            parameters[2].Value = model.CLA_NAME;
            parameters[3].Value = model.CLA_ACADEMY;
            parameters[4].Value = model.CLA_SHORT;
            parameters[5].Value = model.CLA_ENNAME;
            parameters[6].Value = model.CLA_STUNUM;
            parameters[7].Value = model.CLA_INDATE;
            parameters[8].Value = model.CLA_MAJOR;
            parameters[9].Value = model.CLA_SYSTYPE;
            parameters[10].Value = model.CLA_LOCAL;
            parameters[11].Value = model.CLA_REMARK;
            parameters[12].Value = model.CLA_GRADE;
            parameters[13].Value = model.CLA_IsShow;
            parameters[14].Value = model.ACA_NAME;
            parameters[15].Value = model.LOC_NAME;
            parameters[16].Value = model.MAJ_NAME;

            return parameters;
        }
    }
}